using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace produccion.clases
{

public class CLSENCPPCP:ObservableObject
{

#region Metodos basicos tabla

MDOgeneral mod = new MDOgeneral();

//Listar pedidos de un cliente

public DataTable listarPedidoCli() 
{
    try {
        //string query = "select nroped_epc,codcli_epc,''||fecped_epc||'' as fecped,''||fecent_epc||'' as fecent_epc,nomcli_cli,sum(canped_dpc) as canped_dpc,sum(canent_dpc) as canent_dpc,sum(candes_dpc) as candes_dpc,(sum(canent_dpc)-(sum(canped_dpc)-sum(candes_dpc))) as demas, (sum(canped_dpc)-sum(canent_dpc)-sum(candes_dpc)) as pendiente  from encppcp,maeclip,detppcp where (codcli_cli=subcli_epc) and (codcli_dpc=codcli_epc and nroped_dpc=nroped_epc) and (codcli_epc='" + CODCLI_EPC + "') group by nroped_epc,fecped_epc,fecent_epc,nomcli_cli,codcli_dpc,nroped_epc,codcli_epc order by fecped_epc desc";
        string query = "select pe.nroped_epc,pe.codcli_epc,''||pe.fecped_epc||'' as fecped,''||pe.fecent_epc||'' as fecent_epc,cl.NOMCLI_CLI,sum(de.CANPED_DPC) as canped_dpc, sum(de.CANENT_DPC) as canent_dpc,sum(de.candes_dpc) as candes_dpc,(sum(de.canent_dpc)-(sum(de.canped_dpc)-sum(de.candes_dpc))) as demas, (sum(de.canped_dpc)-sum(de.canent_dpc)-sum(de.candes_dpc)) as pendiente, pe.pedori_epc from encppcp  pe inner join  maeclip cl on pe.CODCLI_EPC=cl.CODCLI_CLI inner join detppcp  de on pe.NROPED_EPC=de.NROPED_DPC and pe.CODCLI_EPC=de.CODCLI_DPC where pe.CODCLI_EPC='" + CODCLI_EPC + "' group by pe.NROPED_EPC,pe.CODCLI_EPC, pe.FECPED_EPC, pe.FECENT_EPC,cl.NOMCLI_CLI, pe.pedori_epc order by pe.NROPED_EPC desc";

        return mod.consulta(query);
    }
    catch (Exception ex) { throw ex; }
}


//Listar pedidos de un cliente

public CLSDETPPCP detallePedido = new CLSDETPPCP();
public CLSMAECLIP cliente = new CLSMAECLIP();

public List<CLSENCPPCP> listarPedidos()
{
    try
    {
        string query = "select pe.NROPED_EPC, pe.FECPED_EPC, pe.FECENT_EPC,cl.NOMCLI_CLI,sum(de.CANPED_DPC) as CANPED_DPC, sum(de.CANENT_DPC) as CANENT_DPC from encppcp  pe inner join  maeclip cl on pe.CODCLI_EPC=cl.CODCLI_CLI inner join detppcp  de on pe.NROPED_EPC=de.NROPED_DPC and pe.CODCLI_EPC=de.CODCLI_DPC where pe.CODCLI_EPC='"+CODCLI_EPC+"' group by pe.NROPED_EPC, pe.FECPED_EPC, pe.FECENT_EPC,cl.NOMCLI_CLI order by pe.FECPED_EPC desc";
        DataTable val= mod.consulta(query);
        List<CLSENCPPCP> lista = new List<CLSENCPPCP>();
        foreach (DataRow dr in val.Rows) {
            CLSENCPPCP ped = new CLSENCPPCP();
            ped.NROPED_EPC = Convert.ToInt32(dr["NROPED_EPC"]);
            ped.FECPED_EPC = Convert.ToDateTime(dr["FECPED_EPC"]);
            ped.FECENT_EPC = Convert.ToDateTime(dr["FECENT_EPC"]);
            ped.detallePedido.CANPED_DPC = Convert.ToInt32(dr["CANPED_DPC"]);
            ped.detallePedido.CANENT_DPC = Convert.ToInt32(dr["CANENT_DPC"]);
            ped.cliente.NOMCLI_CLI = dr["NOMCLI_CLI"].ToString();
            lista.Add(ped);
        }
        return lista;
    }
    catch (Exception ex) { throw ex; }
}


//Consulta encabezado del pedido del cliente
public void consultar()
{
    try
    {
        string query = "select CODCLI_EPC,NROPED_EPC,FECPED_EPC,FECENT_EPC,ESTPED_EPC,OBSPED_EPC,PEDORI_EPC,SUBCLI_EPC,DESPED_EPC from ENCPPCP where CODCLI_EPC='"+CODCLI_EPC+"' and NROPED_EPC="+NROPED_EPC;
        DataTable val = mod.consulta(query);
        if (val.Rows.Count > 0)
        {
            this.CODCLI_EPC = val.Rows[0]["CODCLI_EPC"].ToString();


            CODCLI_EPC = val.Rows[0]["CODCLI_EPC"].ToString();
            NROPED_EPC = Convert.ToInt32(val.Rows[0]["NROPED_EPC"]);
            FECPED_EPC =Convert.ToDateTime(val.Rows[0]["FECPED_EPC"]);
            FECENT_EPC = Convert.ToDateTime(val.Rows[0]["FECENT_EPC"]);
            ESTPED_EPC = val.Rows[0]["ESTPED_EPC"].ToString();
            OBSPED_EPC = val.Rows[0]["OBSPED_EPC"].ToString();
            PEDORI_EPC = val.Rows[0]["PEDORI_EPC"].ToString();
            SUBCLI_EPC = val.Rows[0]["SUBCLI_EPC"].ToString();
            DESPED_EPC = val.Rows[0]["DESPED_EPC"].ToString();


            EXISTE = true;
            CAMBIO = false;
        }
        else
        {
            EXISTE = false;
        }

    }
    catch (Exception e) { throw e; }
}

public int actualizar()
{
    try
    {
        string query = "update ENCPPCP set FECPED_EPC='" + FECPED_EPC + "',FECENT_EPC='" + FECENT_EPC + "',ESTPED_EPC='" + ESTPED_EPC + "',OBSPED_EPC='" + OBSPED_EPC + "',PEDORI_EPC='" + PEDORI_EPC + "',SUBCLI_EPC='" + SUBCLI_EPC + "',DESPED_EPC='" + DESPED_EPC + "' where CODCLI_EPC='" + CODCLI_EPC + "' and NROPED_EPC=" + NROPED_EPC;
        return mod.comando(query);
    }
    catch (Exception e) { throw e; }
}

public int ActualizarObservaciones()
{
    try
    {
        string query = "update ENCPPCP set OBSPED_EPC='" + OBSPED_EPC + "' where CODCLI_EPC='" + CODCLI_EPC + "' and NROPED_EPC=" + NROPED_EPC;
        return mod.comando(query);
    }
    catch (Exception e) { throw e; }
}

public int insertar()
{
    try
    {                                                                                                                                                 
        string query = "insert into ENCPPCP (CODCLI_EPC,NROPED_EPC,FECPED_EPC,FECENT_EPC,ESTPED_EPC,OBSPED_EPC,PEDORI_EPC,SUBCLI_EPC,DESPED_EPC) values ('"+CODCLI_EPC+"',"+NROPED_EPC+",'"+FECPED_EPC.ToShortDateString()+"','"+FECENT_EPC.ToShortDateString()+"','"+ESTPED_EPC+"','"+OBSPED_EPC+"','"+PEDORI_EPC+"','"+SUBCLI_EPC+"','"+DESPED_EPC+"')";
        return mod.comando(query);
    }
    catch (Exception e) { throw e; }
}

public Boolean existe() {
  string query="select  coalesce(count(CODCLI_EPC),0) from ENCPPCP where (CODCLI_EPC='"+CODCLI_EPC+"') and (NROPED_EPC="+NROPED_EPC+")";
  DataTable val = mod.consulta(query);
  if (val.Rows[0][0].ToString().Equals("0")){
  return false;
  }
  return true;
}

//Defninir parametro para buscar

public DataTable buscar()
{
    try
    {
        string query = "select NROPED_EPC as Codigo,''||FECPED_EPC||'' as Fecha,ESTPED_EPC AS Estado from  ENCPPCP where CODCLI_EPC='" + CODCLI_EPC + "' and ";

        if (FECPED_FIL!= "")
        {
            query = query + "UPPER(FECPED_EPC) like '%" + FECPED_FIL.ToUpper() + "%' and ";
        }

        query = query.Remove(query.Length - 4).Trim() + " Order by  NROPED_EPC asc";

        return mod.consulta(query);
    }
    catch (Exception e) { throw e; }
}

public Boolean Existe(string Cli, long Ped)
{
    try
    {
        string query = "select codcli_epc from encppcp where codcli_epc='" + Cli + "' and nroped_epc=" + Ped;
        DataTable val = mod.consulta(query);
        if (val.Rows.Count > 0)
        {
            return true;
        }
        return false;
    }
    catch (Exception ex) { throw ex; }
}

public string NombreRef(string Codigo)
{
    try
    {
        string query = "select nomref_ref as descripcion from maeref where codref_ref='" + Codigo + "'";
        if (mod.consulta(query).Rows.Count > 0)
        {
            DESCRIPCION = mod.consulta(query).Rows[0][0].ToString().Trim();
        }
        return DESCRIPCION;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

public DataTable busXsubCli()
{
    try
    {

        string query = "";

        if (String.IsNullOrEmpty(SUBCLI_EPC) && String.IsNullOrEmpty(CODCLI_EPC))
        {
            query = "select NROPED_EPC as Codigo,''||FECPED_EPC||'' as Fecha,ESTPED_EPC AS Estado from  ENCPPCP" + (String.IsNullOrEmpty(FECPED_FIL) ? "" : " where UPPER(FECPED_EPC) like '%" + FECPED_FIL.ToUpper() + "%'") + " Order by  NROPED_EPC asc";
        }


        if (!String.IsNullOrEmpty(CODCLI_EPC))
        {
            query = "select NROPED_EPC as Codigo,''||FECPED_EPC||'' as Fecha,ESTPED_EPC AS Estado from  ENCPPCP where CODCLI_EPC='" + CODCLI_EPC + "'" + (String.IsNullOrEmpty(FECPED_FIL) ? "" : " and UPPER(FECPED_EPC) like '%" + FECPED_FIL.ToUpper() + "%'") + " Order by  NROPED_EPC asc";
       
        }

        if (!String.IsNullOrEmpty(SUBCLI_EPC))
        {
            query = "select NROPED_EPC as Codigo,''||FECPED_EPC||'' as Fecha,ESTPED_EPC AS Estado from  ENCPPCP where SUBCLI_EPC='" + SUBCLI_EPC + "'" + (String.IsNullOrEmpty(FECPED_FIL) ? "" : " and UPPER(FECPED_EPC) like '%" + FECPED_FIL.ToUpper() + "%'") + " Order by  NROPED_EPC asc";
       
        }

        if (!String.IsNullOrEmpty(SUBCLI_EPC) && !String.IsNullOrEmpty(CODCLI_EPC))
        {
            query = "select NROPED_EPC as Codigo,''||FECPED_EPC||'' as Fecha,ESTPED_EPC AS Estado from  ENCPPCP where SUBCLI_EPC='" + SUBCLI_EPC + "' and CODCLI_EPC='" + CODCLI_EPC + "'" + (String.IsNullOrEmpty(FECPED_FIL) ? "" : " and UPPER(FECPED_EPC) like '%" + FECPED_FIL.ToUpper() + "%'") + " Order by  NROPED_EPC asc";
       
        }

        return mod.consulta(query);
    }
    catch (Exception e) { throw e; }
}

public DataTable BuscarPed()
{
    try
    {
        string query = "select nroped_epc as Codigo from encppcp where codcli_epc='" + CODCLI_EPC + "'";
        DataTable val = mod.consulta(query);
        if (val.Rows.Count > 0)
        {
            return val;
        }
        return val;
    }
    catch (Exception ex) { throw ex; }
}


public int eliPedido() {
    try {

        string query = "select coalesce(sum(canent_dpc),0) as canent_dpc, coalesce(sum(candes_dpc),0) as candes_dpc from detppcp where codcli_dpc='" + CODCLI_EPC + "' and nroped_dpc=" + NROPED_EPC;
        DataTable resultado = mod.consulta(query);
        int canent=0, candes=0;
        if (resultado.Rows.Count == 1) {
            canent = Convert.ToInt32(resultado.Rows[0]["canent_dpc"]);
            candes = Convert.ToInt32(resultado.Rows[0]["candes_dpc"]);
        }
        if (canent > 0 || candes > 0)
        {
            return 0;
        }
        else {

            MDOgeneral.transaccion = MDOgeneral.conexion.BeginTransaction();


            query = "delete from encppcp where codcli_epc='"+CODCLI_EPC+"' and nroped_epc="+NROPED_EPC;
            mod.comando(query);
            query="delete from detppcp where codcli_dpc='"+CODCLI_EPC+"' and nroped_dpc="+NROPED_EPC;
            mod.comando(query);
            MDOgeneral.transaccion.Commit();
            return 1;
        
        }

    }
    catch (Exception ex) {

        MDOgeneral.transaccion.Rollback(); 
        throw ex;
    }


}

public void ConsecPed() 
{
    try 
    {
        string query = "select  coalesce(max(nroped_epc),0)+1  from encppcp where codcli_epc='"+CODCLI_EPC+"'";
        NROPED_EPC = Convert.ToInt32(mod.consulta(query).Rows[0][0]);
    }
    catch (Exception ex) { throw ex; }
}

public int crearPedido(List<CLSDETPPCP> lisdetped) 
{
    try 
    {     
        MDOgeneral.transaccion = MDOgeneral.conexion.BeginTransaction();
        ConsecPed();
        
        insertar();
        for(int i=0;i<=lisdetped.Count-1;i++){
            lisdetped[i].NROPED_DPC = NROPED_EPC;
            lisdetped[i].insertar();
        }

        MDOgeneral.transaccion.Commit();

        return 1;
    }
    catch (Exception ex) 
    {
        MDOgeneral.transaccion.Rollback();
        throw ex;
    }

}
#endregion

#region  Propiedades tabla ENCPPCP

    private string _CODCLI_EPC;
    public string CODCLI_EPC{
        get { return _CODCLI_EPC;}
        set {
            _CODCLI_EPC = value;
            RaisePropertyChangedEvent("CODCLI_EPC");
        }
    }
    private int _NROPED_EPC;
    public int NROPED_EPC{
        get { return _NROPED_EPC;}
        set {
            _NROPED_EPC=value;
            RaisePropertyChangedEvent("NROPED_EPC");
        }
    }
    private DateTime _FECPED_EPC = MDOgeneral.DttFechaActualDeValidacionEnElSistema;//FJRF - Ponemos la fecha del dia actual del mes seleccionado por el sistema
    public DateTime FECPED_EPC
    {
        get { return _FECPED_EPC;}
        set {
            if (_FECPED_EPC != value) { CAMBIO = true; }
            _FECPED_EPC=value;
            RaisePropertyChangedEvent("FECPED_EPC");
        }
    }
    private DateTime _FECENT_EPC = MDOgeneral.DttFechaActualDeValidacionEnElSistema;//FJRF - Ponemos la fecha del dia actual del mes seleccionado por el sistema
    public DateTime FECENT_EPC
    {
        get { return _FECENT_EPC;}
        set {
            if (_FECENT_EPC != value) { CAMBIO = true; }
            _FECENT_EPC=value;
            RaisePropertyChangedEvent("FECENT_EPC");
        }
    }
    private string _ESTPED_EPC;
    public string ESTPED_EPC{
        get { return _ESTPED_EPC;}
        set {
            if (_ESTPED_EPC != value) { CAMBIO = true; }
            _ESTPED_EPC=value;
            RaisePropertyChangedEvent("ESTPED_EPC");
        }
    }
    private string _OBSPED_EPC;
    public string OBSPED_EPC{
        get { return _OBSPED_EPC;}
        set {
            if (_OBSPED_EPC != value) { CAMBIO = true; }
            _OBSPED_EPC=value;
            RaisePropertyChangedEvent("OBSPED_EPC");
        }
    }
    private string _PEDORI_EPC;
    public string PEDORI_EPC{
        get { return _PEDORI_EPC;}
        set {
            if (_PEDORI_EPC != value) { CAMBIO = true; }
            _PEDORI_EPC=value;
            RaisePropertyChangedEvent("PEDORI_EPC");
        }
    }
    private string _SUBCLI_EPC;
    public string SUBCLI_EPC{
        get { return _SUBCLI_EPC;}
        set {
            if (_SUBCLI_EPC != value) { CAMBIO = true; }
            _SUBCLI_EPC=value;
            RaisePropertyChangedEvent("SUBCLI_EPC");
        }
    }
    private string _DESPED_EPC = "N";
    public string DESPED_EPC{
        get { return _DESPED_EPC;}
        set {
            if (_DESPED_EPC != value) { CAMBIO = true; }
            _DESPED_EPC=value;
            RaisePropertyChangedEvent("DESPED_EPC");
        }
    }
    private string _FECPED_FIL;
    public string FECPED_FIL
    {
        get { return _FECPED_FIL; }
        set
        {
            if (_FECPED_FIL != value) { CAMBIO = true; }
            _FECPED_FIL = value;
            RaisePropertyChangedEvent("FECPED_FIL");
        }
    }

    private Boolean _CAMBIO = false;
    public Boolean CAMBIO
    {
        get { return _CAMBIO; }
        set
        {
            _CAMBIO = value;
            RaisePropertyChangedEvent("CAMBIO");
        }
    }
    private Boolean _EXISTE = false;
    public Boolean EXISTE
    {
        get { return _EXISTE; }
        set
        {
            _EXISTE = value;
            RaisePropertyChangedEvent("EXISTE");
        }
    }
    string descripcion;
    public string DESCRIPCION
    {
        get { return descripcion; }
        set { descripcion = value; }
    }


#endregion

   }
}
